Custom Views

Custom Views are similar to SQL views, i.e. they allow you to create a virtual table holding the data resulting from the execution of a specified SQL Select statement. In Digitise Apps, the SQL statement can extract data from any of the local SQLite tables used to store your Data Source records on the device and the resulting View can be used as input mappings for Controls on your forms. You can extract records from more than one Data Source if required and present the results as if they were held in a single table.

You can use Custom Views to filter or rearrange records in a Data Source, to present them in a different order to that in which they are stored in the database or to display a sub-set of records in one or more Data Sources.

For example, suppose you are developing a Digitise app to allow field workers to download a list of outstanding jobs and you have a Data Source which holds a list of all the outstanding jobs, both allocated and unallocated. You could use a Custom View to display only those jobs assigned to the current user or only jobs which are scheduled for today etc. Suppose also that you have a second Data Source which holds additional information about your clients, such as their addresses and phone numbers. You could use a Custom View to combine the relevant phone numbers from the second Data Source with jobs in the first Data Source to display a list of jobs with contact details.

 

In order to use a Custom View within your app you need to define the data columns which the View will contain and specify the SQL statement which should be used to populate the View. The SQL statement should be a SQL SELECT statement compatible with SQLite. You can include a 'where' clause in the statement or leave this out. You can add a 'where' clause later or overwrite the original 'where' clause programmatically within your Digitise Apps Scripts allowing you to customise the View at runtime if required – see the SetSelectString Scripting Method for details of how to specify the 'where' clause.

When an app is closed, any Custom Views used in the app will be dropped and recreated using the default SQL Select statement specified when you first created the View so that the app will always start with your default record set.

 

Once you have created a Custom View, you can use the data items in the View in the input mappings for Controls on your forms in the same way as Custom Table data items. You can also use Views with some of the Scripting Methods in your Scripts, e.g. to obtain the number of records in the View, move to the first or last record, update the value displayed in a Control etc. The Method descriptions will tell you if you can use a View with the Method. You cannot, however, edit or add records nor upload data from the device using a View.

When using a Custom View within a Script, View names are referenced in the Script with the prefix Custom, e.g. Custom.MyCustomView.

 

Once you have created a Custom View, the View is always available and you don't need to do anything specifically to update or refresh the View within your Scripts, this is all handled automatically. A View will always show you the latest data stored in the relevant database tables.

 

You can create a Custom View in one of the following ways:

• Using the New Data Source option and manually defining the columns to be included in the View.

  Choose New Data Source from the Project group of the Ribbon's Home tab, and then choose Custom View... from the menu displayed.

  Alternatively, right-click on the Custom Views node in the Data Sources Pane and choose New Data Source → Custom View....

• Using the Create as Custom View… option to copy an existing Data Source or custom View to a new Custom View.

  Right-click on an existing Data Source Table in the Data Sources Pane and choose Create as Custom View… from the menu displayed.

 

In either case, the New Custom View dialog box will be displayed:

If you used the Create as Custom View option, this dialog will include a column in the new View for each data item in the original Data Source and the SQL Select statement will be pre-populated to select all data items. You can edit the SQL Statement, add additional data items to the View or delete items from the original Data Source that you don't want in this Custom View should you want to. You can do this in the same way as described below for a new View.

If you used the New Data Source option, the Columns section will be empty, as show above.

 

Enter a name for this Data Source in the View Name field at the bottom of the dialog box.

To add a new column to your Custom View, click on the Add button below the Columns list. A new column will be added to the list and you can then edit the values for it by clicking in each table cell in turn that you want to edit.

 

To change an existing column, click on the Name or Data Type cell in the affected row to display an edit box or drop-down list, respectively. After changing the column Name, click elsewhere in the table to accept the change or press Esc to cancel the change.

To delete a column from the Table, select the column in the Columns table and then choose the Remove button.

 

You now need to specify the SQL statement to be used to extract data for this View in the SQL field at the top of the dialog box.

The statement must be a SQL SELECT statement compatible with SQLite, with or without a WHERE clause. The statement acts on the local SQLite Tables holding your data on the device and not on the remote data sources. Consequently, you need to use the Table and Data Item names as they appear in the Data Sources Pane. You can use JOINs to combine data from multiple database tables if you want to. Within the Select string columns can be referred to using the format <table>.<column> but <table>.* is not supported.

At runtime you can add a 'where' clause or overwrite the 'where' clause included in your original SQL statement using the SetSelectString Scripting Method within your Scripts, allowing you to customise the SQL statement at any time.

Remember, the column names in the View must match the column names in the database, and hence within your SQL Select statement, unless you give a column a SQL alias within your Select Statement using the AS keyword, in which case the column name within the View must match the alias. Columns don't need to be listed in the SQL Select statement in the same order as they appear within the Custom View.

 

When you have finished creating your Custom View, click on the OK button.

 

Your new View will now be listed in the Data Sources Pane under Custom Views – click on the Data Sources tab to display this pane if it isn't already in view. The View will display in the tree view which you can expand and contract in the usual way.

 

You can edit a Custom View from the Data Sources Pane by right-clicking on the Custom View name and choosing Update Schema. This will display the Edit Custom View dialog box which is effectively the same as the New Custom View dialog box described above.

If you only want to edit the name of a data item or change its data type, you can do this by clicking on the data item in the Data Sources Pane to display its Properties in the Properties Pane and then edit the appropriate values in this Pane.